package cn.itcast.dao;

import cn.itcast.domain.StuAndCourse;
import cn.itcast.domain.Student;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface StudentDao {

    // 查询所有账户
    @Select("select * from student")
    public List<Student> findAll();

    // 根据用户名和密码查询用户
    @Select("select * from student where sno = #{username} and password = #{password}")
    public Student findByStuAndPas(@Param("username") String username,
                                   @Param("password") String password);

    // 根据id查询成绩
    @Select("SELECT student.`sno`, student.`sname`, student.`sex`," +
            " sc.`courseno`,course.`coursename`,sc.`score` FROM student,sc,course " +
            "WHERE  sc.`sno` = student.`sno` " +
            "AND sc.`courseno` = course.`courseno` " +
            "AND student.`sno` = #{username}" )
    public List<StuAndCourse> findByIdtoCourse(String username);

    // 根据id查询个人信息
    @Select("SELECT * FROM student WHERE sno = #{username}")
    public Student findById(@Param("username")String username);

    // 修改密码
    @Update("update student set password = #{confirmpassword} where sno = #{username}")
    public void updatePass(@Param("oldpassword")String oldpassword,
                           @Param("newpassword")String newpassword,
                           @Param("confirmpassword")String confirmpassword,
                           @Param("username")String username);


}
